﻿Public Class clsDbSamplingDef

    ''' <summary>
    ''' サンプル数量マスタ未設定パラメータ
    ''' </summary>
    Private Const DB_PARAM_DEFAULT As String = "*"
    ''' <summary>
    ''' 非表示時の返却パラメータ
    ''' </summary>
    Private Const RETURN_DEFAULT As String = "-"

    ''' <summary>
    ''' LTF画面の表示数定義
    ''' </summary>
    Private Const LTF_VIEW_COUNT As Integer = 10

    ''' <summary>
    ''' エラーメッセージの表示言語
    ''' </summary>
    Private mlang As String
    Public Sub New(ByVal lang As String)
        mlang = lang
    End Sub

    ''' <summary>
    ''' サンプル数量定義マスタテーブル取得
    ''' </summary>
    ''' <param name="ProcCd_">工程コード</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <param name="EquipNo_">装置No</param>
    ''' <returns></returns>
    Private Function getSamplingDefTable(ByVal ProcCd_ As String, ByVal AggregatePkg_ As String, ByVal EquipNo_ As String) As DataTable
        Dim szSql = String.Empty

        szSql &= "SELECT * FROM PER.T_SAMPLING_DEF WHERE PROC_CD = ? AND AGGREGATE_PKG = ? AND EQUIP_NO = ?"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, ProcCd_, AggregatePkg_, EquipNo_)
        If IsNothing(dt) Then
            getSamplingDefTable = New DataTable
        Else
            getSamplingDefTable = dt
        End If
    End Function

    ''' <summary>
    ''' サンプルキー取得
    ''' </summary>
    ''' <param name="ProcCd_">工程コード</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <param name="EquipNo_">装置No</param>
    ''' <returns></returns>
    Private Function getSamplingKey(ByVal ProcCd_ As String, ByVal AggregatePkg_ As String, ByVal EquipNo_ As String) As String
        Dim szSql = String.Empty

        szSql &= "SELECT * FROM PER.T_SAMPLING_DEF WHERE PROC_CD = ? AND AGGREGATE_PKG = ? AND EQUIP_NO = ?"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, ProcCd_, AggregatePkg_, EquipNo_)
        Dim getData = dt.AsEnumerable().FirstOrDefault()
        If IsNothing(getData) Then
            getSamplingKey = String.Empty
        Else
            getSamplingKey = getData("sample_key")
        End If
    End Function

    ''' <summary>
    ''' 投入数量毎サンプル数の取得
    ''' </summary>
    ''' <param name="key">サンプルキー</param>
    ''' <param name="inputNum">投入数</param>
    ''' <returns></returns>
    Private Function getSampleNum(ByVal key As String, ByVal inputNum As Integer) As String
        Dim szSql = String.Empty

        szSql &= "SELECT * FROM PER.T_LOT_SAMPLE_NUM_DEF WHERE SAMPLE_KEY = ? AND LOT_SIZE <= ?"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, key, inputNum)
        Dim maxSize = dt.AsEnumerable().Max(Function(x) x("lot_size"))
        Dim maxItem = dt.AsEnumerable().SingleOrDefault(Function(x) x("lot_size") = maxSize)
        If IsNothing(maxItem) Then
            getSampleNum = String.Empty
        Else
            getSampleNum = maxItem("sample_num")
        End If
    End Function

    ''' <summary>
    ''' サンプル数量ロット履歴テーブルの取得
    ''' </summary>
    ''' <param name="key">サンプルキー</param>
    ''' <param name="pkg">集約PKG</param>
    ''' <param name="equipNo">装置No</param>
    ''' <returns></returns>
    Private Function getSampleLotTable(ByVal key As String, ByVal pkg As String, ByVal equipNo As String) As DataTable
        Dim szSql = String.Empty

        szSql &= "SELECT * FROM PER.L_SAMPLING_LOT WHERE SAMPLE_KEY = ? AND AGGREGATE_PKG = ? AND EQUIP_NO = ?"

        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1, key, pkg, equipNo)
        If IsNothing(dt) Then
            getSampleLotTable = New DataTable
        Else
            getSampleLotTable = dt
        End If
    End Function

    ''' <summary>
    ''' サンプル数量ロット履歴の該当レコード数取得
    ''' </summary>
    ''' <param name="key">サンプルキー</param>
    ''' <param name="pkg">集約PKG</param>
    ''' <param name="equipNo">装置No</param>
    ''' <returns></returns>
    Private Function getSampleLotCount(ByVal key As String, ByVal pkg As String, ByVal equipNo As String) As Integer
        Dim dt = getSampleLotTable(key, pkg, equipNo)
        getSampleLotCount = dt.AsEnumerable().Count
    End Function

    ''' <summary>
    ''' サンプル数量ロット履歴のレコード数による表示可否判定
    ''' レコード数が0件または10件の場合に表示可能とする
    ''' </summary>
    ''' <param name="key">サンプルキー</param>
    ''' <param name="pkg">集約PKG</param>
    ''' <param name="equipNo">装置No</param>
    ''' <returns></returns>
    Private Function IsSampleLotView(ByVal key As String, ByVal pkg As String, ByVal equipNo As String) As Boolean
        Dim lotCount = getSampleLotCount(key, pkg, equipNo)
        IsSampleLotView = lotCount >= LTF_VIEW_COUNT Or lotCount.Equals(0)
    End Function

    ''' <summary>
    ''' サンプル数量ロット履歴テーブルへの登録
    ''' </summary>
    ''' <param name="Key_">サンプルキー</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <param name="EquipNo_">装置No</param>
    ''' <param name="LotNo_">組立ロットKey</param>
    Private Sub insertSampleingLot(ByVal Key_ As String, ByVal AggregatePkg_ As String, ByVal EquipNo_ As String, ByVal LotNo_ As String)
        Dim szSql = String.Empty
        szSql &= "INSERT INTO PER.L_SAMPLING_LOT( "
        szSql &= "  SAMPLE_KEY, AGGREGATE_PKG, EQUIP_NO, LOT_NO"
        szSql &= ") "
        szSql &= "VALUES ( ?, ?, ?, ?) "
        DBacs.ExecuteUpdateSql(szSql, -1, Key_, AggregatePkg_, EquipNo_, LotNo_)
    End Sub
    ''' <summary>
    ''' サンプル数量ロット履歴の該当レコード削除
    ''' </summary>
    ''' <param name="Key_">サンプルキー</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <param name="EquipNo_">装置No</param>
    Private Sub deleteSampleingLot(ByVal Key_ As String, ByVal AggregatePkg_ As String, ByVal EquipNo_ As String)
        Dim szSql = String.Empty
        szSql &= "DELETE  FROM PER.L_SAMPLING_LOT WHERE SAMPLE_KEY = ? AND AGGREGATE_PKG = ? AND EQUIP_NO = ?"
        DBacs.ExecuteUpdateSql(szSql, -1, Key_, AggregatePkg_, EquipNo_)
    End Sub

    ''' <summary>
    ''' サンプル数量ロット履歴更新処理
    ''' サンプル数量ロット履歴テーブルの状態によって削除を行う
    ''' </summary>
    ''' <param name="ProcCd_">工程コード</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <param name="EquipNo_">装置No</param>
    ''' <param name="LotNo_">組立ロットKey</param>
    Public Sub updateSampleingLot(ByVal ProcCd_ As String, ByVal AggregatePkg_ As String, ByVal EquipNo_ As String, ByVal LotNo_ As String)
        Dim sampleKey = getSamplingKey(ProcCd_, AggregatePkg_, DB_PARAM_DEFAULT)
        Dim sampleLotDt = getSampleLotTable(sampleKey, AggregatePkg_, EquipNo_)
        ' 同一レコードが登録済みであれば更新しないで処理終了
        If sampleLotDt.AsEnumerable().Any(Function(x) x("lot_no").Equals(LotNo_)) Then
            Exit Sub
        End If
        ' 10件登録済みなら全レコードクリア
        If IsSampleLotView(sampleKey, AggregatePkg_, EquipNo_) Then
            deleteSampleingLot(sampleKey, AggregatePkg_, EquipNo_)
        End If
        ' レコード登録
        If sampleKey.Equals("") = False Then 'ADD MIAO 2018/10/23
            insertSampleingLot(sampleKey, AggregatePkg_, EquipNo_, LotNo_)
        End If
    End Sub

    ''' <summary>
    ''' LTF画面のサンプル数量表示文字列を取得
    ''' LTF画面はN回に1回表示する仕様のため、表示が必要かを判定して文字列を返却する。
    ''' </summary>
    ''' <param name="ProcCd_">工程コード</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <param name="EquipNo_">装置No</param>
    ''' <param name="InputNum_">投入数</param>
    ''' <returns></returns>
    Public Function getSamplingMessageLTF(ByVal ProcCd_ As String, ByVal AggregatePkg_ As String, ByVal EquipNo_ As String, ByVal InputNum_ As String) As String
        Dim sampleKey = getSamplingKey(ProcCd_, AggregatePkg_, DB_PARAM_DEFAULT)
        If IsSampleLotView(sampleKey, AggregatePkg_, EquipNo_) Then
            getSamplingMessageLTF = getSamplingMessage(ProcCd_, AggregatePkg_, InputNum_)
        Else
            getSamplingMessageLTF = RETURN_DEFAULT
        End If
    End Function

    ''' <summary>
    ''' サンプル数量表示文字列取得
    ''' </summary>
    ''' <param name="ProcCd_">工程コード</param>
    ''' <returns></returns>
    Public Function getSamplingMessage(ByVal ProcCd_ As String) As String
        Dim SamplingDef = getSamplingDefTable(ProcCd_, DB_PARAM_DEFAULT, DB_PARAM_DEFAULT)
        Dim getData = SamplingDef.AsEnumerable().FirstOrDefault()
        If getData IsNot Nothing Then
            getSamplingMessage = getData("message")
        Else
            getSamplingMessage = getMsgStr("MSG10023")
        End If
    End Function

    ''' <summary>
    ''' サンプル数量表示文字列取得
    ''' </summary>
    ''' <param name="ProcCd_">工程コード</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <returns></returns>
    Public Function getSamplingMessage(ByVal ProcCd_ As String, ByVal AggregatePkg_ As String) As String
        Dim SamplingDef = getSamplingDefTable(ProcCd_, AggregatePkg_, DB_PARAM_DEFAULT)
        Dim getData = SamplingDef.AsEnumerable().FirstOrDefault()
        If getData IsNot Nothing Then
            getSamplingMessage = getData("message")
        Else
            getSamplingMessage = getMsgStr("MSG10023")
        End If
    End Function

    ''' <summary>
    ''' サンプル数量表示文字列取得
    ''' </summary>
    ''' <param name="ProcCd_">工程コード</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <param name="InputNum_">投入数</param>
    ''' <returns></returns>
    Public Function getSamplingMessage(ByVal ProcCd_ As String, ByVal AggregatePkg_ As String, ByVal InputNum_ As String) As String
        Dim SamplingDef = getSamplingDefTable(ProcCd_, AggregatePkg_, DB_PARAM_DEFAULT)
        Dim getData = SamplingDef.AsEnumerable().FirstOrDefault()
        If getData IsNot Nothing Then
            getSamplingMessage = getSampleKeyMessage(InputNum_, getData)
        Else
            getSamplingMessage = getMsgStr("MSG10023")
        End If
    End Function

    ''' <summary>
    ''' サンプル数量表示文字列取得
    ''' </summary>
    ''' <param name="ProcCd_">工程コード</param>
    ''' <param name="AggregatePkg_">集約PKG</param>
    ''' <param name="EquipNo_">装置No</param>
    ''' <param name="InputNum_">投入数</param>
    ''' <returns></returns>
    Public Function getSamplingMessage(ByVal ProcCd_ As String, ByVal AggregatePkg_ As String, ByVal EquipNo_ As String, ByVal InputNum_ As String) As String
        Dim SamplingDef = getSamplingDefTable(ProcCd_, AggregatePkg_, EquipNo_)
        Dim getData = SamplingDef.AsEnumerable().FirstOrDefault()
        If getData IsNot Nothing Then
            getSamplingMessage = getSampleKeyMessage(InputNum_, getData)
        Else
            getSamplingMessage = getMsgStr("MSG10023")
        End If
    End Function

    ''' <summary>
    ''' サンプルキーを使う場合のメッセージ取得関数
    ''' </summary>
    ''' <param name="InputNum_">投入数</param>
    ''' <param name="getData">サンプル数量定義マスタの該当レコードデータ</param>
    ''' <returns>表示文字列</returns>
    Private Function getSampleKeyMessage(InputNum_ As String, ByVal getData As DataRow) As String
        Dim sampleKey = getData("sample_key")
        If String.IsNullOrEmpty(DBNullTOblank(sampleKey)) Then 'UPD MIAO 2018/10/24
            getSampleKeyMessage = getData("message")
        Else
            Dim sampleNum = getSampleNum(sampleKey, InputNum_)
            If String.IsNullOrEmpty(sampleNum) Then
                getSampleKeyMessage = getMsgStr("MSG10023")
            Else
                getSampleKeyMessage = String.Format(getData("message"), sampleNum)
            End If
        End If
    End Function
    'ADD MIAO 2018/10/17 Begin
    Public Function DBNullTOblank(obj As Object) As String
        DBNullTOblank = IIf(IsDBNull(obj), "", obj)
    End Function
    'ADD MIAO 2018/10/17 END
End Class
